<?php
/*
 * -File    $Id: sqldatasource.php,v 1.10 2005/04/29 01:47:57 amadeus Exp $
 * -License LGPL (http://www.gnu.org/copyleft/lesser.html)
 * -Copyright   2006, Nexista
 * -Author  joshua savage, js@nexista.com
 * -Author 	albert lash, alby@thirteen.net
 */


/**
 * @package     Nexista
 * @subpackage  Datasources
 * @author      Joshua Savage <js@nexista.com>
 */
 
/**
 * This class provides functionality to access
 * sql databases through the MDB2 abstraction
 * layer.
 *
 * @tutorial    query.pkg
 * @package     Nexista
 * @subpackage  Datasources
 */

class mdb2SqlDatasource
{

    /**
     * Class parameters
     *
     * @var     array
     */

    private $params;


    /**
     * Query info
     *
     * @var     array
     */

    private $query;


    /**
     * Database handle
     *
     * @var     int
     */

    static public $db;


    /**
     * Query result reference
     *
     * @var     object
     */

    private $result;


    /**
     * Temporary array for row result
     *
     * @var     array
     */

    private $rowResult;


    /**
     * Name of query
     *
     * This name is set in the query file under
     * <query name="..."> and is used to reference
     * The query results in Flow (i.e. //queryname/datavar)
     *
     * @var     string
     */

    private $queryName;


    /**
     * Type of query
     *
     * The type of query (select, insert, etc) This is a
     * marker used to speed process internally
     *
     * @var     string
     */

    private $queryType;


    /**
     * Array of active lob references
     *
     *
     * @var     array
     */

    private $lob;

    /**
     * Constructor
     *
     * @param   array       connection parameters
     * @return  boolean     success
     */

    public function Mdb2SqlDatasource(&$params)
    {
            $this->params =& $params;
    }


    /**
     * Establishes datasource connections settings
     *
     * @return  boolean     success
     */

    public function setConnection()
    {
		
        require_once("MDB2.php");

		$dsn = array(
			'persistent' => true, 
            "hostspec"=>$this->params['hostname'],
            "phptype"=>"mysqli",
            "username"=>$this->params['username'],
            "password"=>$this->params['password'],
            "database"=>$this->params['database']);

		$this->db =& MDB2::connect($dsn);
		if (PEAR::isError($this->db)) {
			die($this->db->getMessage());
		}
		
        if($error!="")
        {
            Error::init("Database setup error: $error", NX_ERROR_FATAL);
        }

        return true;
    }


    /**
     * Parses a prepared query for special var types, action codes (_AUTO_, _DATE_, etc)
     * and for general query type (select,insert,etc...) and
     *
     * This method calls metabase prepareQuery(), sets the variable types
     * (see metabase docs) and parses a query to determine its type (select,insert.etc...)
     * and updates $this->queryType with this info. It also looks for
     * special keyword and replaces them with values accordingly.
     * _AUTO_ : retrieves sequnce info (auto-increment) for the table,
     * increments it and returns the new value.
     * _DATE_ : inserts current timestamp
     * This functiomn returns a prepared query handler resource by reference
     *
     * @param   integer     returning prepared query handler
     * @param   integer     current loop count
     * @return  boolean     success
     */

    private function prepareQuery(&$sql, $loop)
    {
        //see if it is a select
        if (eregi("^[[:space:]]*select", $this->query['sql']))
        {
            $this->queryType = 'select';
        }
        else
        {
            //parse query for any sequence and set it
            $this->setKeywords();
        }

        $count = 1;


        if (isset($this->query['params']))
        {       

            foreach($this->query['params'] as $val)
            {

                $found = true;
                $path = new Path();
                if(!empty($val['name']))
                {           
                  
                    $value = $path->get($val['name'], 'flow');
                  
                    if(is_null($value)  && ($val['type'] == 'integer') && $value!=='0')
                    {
                         $found = false;
                    }                    
                }

                elseif(!empty($val['array']))
                {
             
                    $array = $path->get($val['array'], 'flow');                    
                    
                    if(!is_array($array))
                        $array = array($array);
                    
                    $value = $array[$loop];
                    
                }
               
                else
                    $found = false;

                    
                if(((!$found) || ($value === 'NaN') || ($value === '') || ($value == '')) && $value!=='0')
                {
                    $value = $val['default'];

                }

                //set the null value if any
                if($value === 'NULL')
                {
                    $type = 'null';
                }
                else
                {
                    $type = $val['type'];
                }



                if($value || $value==0)
                {
					$types[] = $type;
					$data[] = $value;
                }
				$type = NULL;
                $count++;
            }
			$debug===false;
			//$prep = $this->db->prepare($this->query['sql'],$types,MDB2_PREPARE_RESULT);
			if($debug===true) { 
			print_r($data);
			echo "<pre>";
			echo $this->query['sql'];
			echo "</pre>";
			//echo "<br/>";
			}
			$prep = $this->db->prepare($this->query['sql'], $types);
			if($this->db->isError($prep)) { 
			    echo $this->query['sql'];
				echo "<br/><br/>";
				echo "<pre>";
				print_r($types);
				echo "<br/><br/>";
				print_r($data);
				echo "<br/><br/>";
				print_r($this->query['sql']);
				echo "<br/><br/>";
				print_r($prep);
				echo "<br/><br/>";
				echo "</pre>";
				$this->db->disconnect();
				//exit;
			}
			$result = $prep->execute($data);
			if($debug===true) { 
				print_r($result);
			}
           
        }

        return $result;
    }


    /**
     * Shutdown and resource clear
     *
     */

    public function __destruct()
    {
    

    }



    /**
     * Executes a query
     *
     * @param   mixed       query
     * @param   string      name of query
     * @param   integer     loop count
     * @return  boolean     success
     */

    public function execQuery($query, $queryName, $queryloop)
    {

         // What is this?
        $this->query =& $query;
        $this->queryName =& $queryName;
		

        for($loop = 0; $loop < $queryloop; $loop ++)
        {
            if(!$this->result=$this->prepareQuery($sql, $loop))
            {
                    return false;
            }

            if ($this->queryType == 'select')
            {

                  $this->storeResult();
            }
          
            //free resources for this query
			$this->result->free();
				
        }

        return true;


    }


    /**
     * Parses headers with query result
     *
     * This is a callback function used to parse lob headers with db data.
     *
     * @param   array       match result from parsing lob headers
     * @return  string      array element
     */

    private function resultParserCallback($match)
    {
        return $this->rowResult[$match[1]];
    }


    /**
     * Assigns query result to flow
     *
     * @return  boolean success
     */

    public function storeResult()
    {
    
		$debug = false;
        //TODO is it possible to patch metabase so that it intefaces directly with flow? check.
        if($this->result != 0)
        {
			
            $cols = $this->result->getColumnNames();
			$row = 0;
			$number_of_rows=$this->result->numRows();
			//echo $number_of_rows;
            while($row < $number_of_rows)
            {
				if($debug===true){ 
				print_r($cols);
				print_r($this->queryName);
				}
                $flow = Flow::singleton();
                $q = $flow->root->appendChild($flow->flowDocument->createElement($this->queryName));

                foreach($cols as $key => $val)
                {

					
                    //currently we ignore null values in flow (no variable = null, empty variable = blank string)
                    //if(!$this->db->resultIsNull($this->result, $row, $key))
                    //{	
						$myval = $this->result->FetchOne($val,$row);
						if($debug===true) {
						echo "Stuff: $key, $myval<br/>";
						}
						//$myval = iconv("UTF-8","UTF-8//IGNORE",$myval);
						$myval = htmlspecialchars($myval);
                        $q->appendChild($flow->flowDocument->createElement($key,$myval));


                    //}
                }
				$row++;

            }
			
			

            return true;

        }
        return false;


    }


    /**
     * Parses insert queries for any sequence (_AUTO_)
     * and replaces them with appropriate values
     *
     */

    public function setKeywords()
    {
        //TODO this needs to be extensively tested with different query phrasings
        if(preg_match("~INSERT.*\(\s*(\w.*)\s*\).*VALUES\s*\(\s*(\w.*)\s*\)~s", $this->query['sql'], $matches))
        {
            //Debug::dump($matches);
            $qry_val = preg_split ('~[\s]*,[\s]*~', $matches[2]);

            //_AUTO_ (auto increment sequence)
            $seq = array_search('_AUTO_', $qry_val);

            if($seq !== false)
            {
            
                $qry_name = preg_split ('~[\s]*,[\s]*~', $matches[1]);
                $seq_name = $qry_name[$seq];
				$seq_name = "_sequence_".$seq_name.".sequence";
				
				$idgen = $this->db->getIdGenerator();
				print_r($idgen);
		
				$id = $idgen->getId();
				echo "$seq_name, $id";
                //replace _AUTO_ with new sequence value
                $this->query['sql'] = str_replace('_AUTO_', $id, $this->query['sql']);
                
            }
        }

        return true;
    }


    /**
     * Retrieves db field/column name from a query
     *
     * @param   integer     count of desired column in query
     * @return  string      name of column in db
     */

     private function getFieldName($count)
     {
        //TODO this needs to be extensively tested with different query phrasings
        $field = false;
        //see if INSERT
        if(stristr($this->query['sql'], 'INSERT'))
        {
            //get query row names and values
            preg_match("~INSERT.*\(\s*(\w.*)\s*\).*VALUES\s*\(\s*(\w.*)\s*\)~m", $this->query['sql'], $fields);

            $qry_val = preg_split ('~[\s]*,[\s]*~', $fields[2]);
            $qry_name = preg_split ('~[\s]*,[\s]*~', $fields[1]);

            for($i = 0; $i < sizeof($qry_name); $i++)
            {
                if($qry_val[$i] != '?')
                {
                    //offset count for this value
                    $count ++;
                }
                elseif($i == $count)
                {
                    $field = trim($qry_name[$i]);
                    break;
                }
            }
        }
        //assume UPDATE
        else
        {

            preg_match("~SET\s*(\w.*(.[^,]))\s+([^\W,])~Um", $this->query['sql'], $fields);
            $fields = preg_split ('~[\s]*,[\s]*~', $fields[1]);

            for($i = 0; $i < sizeof($fields); $i++)
            {
                if(!stristr($fields[$i], '?'))
                {
                    //offset count for this value
                    $count ++;
                }
                elseif($i == $count)
                {

                    $field = split('=', $fields[$count]);
                    $field = trim($field[0]);
                    break;
                }
            }
        }

        return $field;

     }


    /**
     * Creates a unique query name for naming cache files
     *
     * @return      integer     crc32 of prepared query array
     */

    public function getQueryID()
    {

        return md5(serialize($this->db->prepared_queries));

    }
}
?>
